package com.familyaccount.service;

import com.familyaccount.model.Transaction;
import com.familyaccount.util.DatabaseUtil;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.YearMonth;
import java.util.*;

public class TransactionService {
    
    public BigDecimal getMonthlyIncome(int year, int month) throws SQLException {
        String sql = "SELECT COALESCE(SUM(amount), 0) FROM transactions " +
                    "WHERE type = 'INCOME' AND YEAR(transaction_date) = ? AND MONTH(transaction_date) = ?";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, year);
            pstmt.setInt(2, month);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return rs.getBigDecimal(1);
            }
            return BigDecimal.ZERO;
        }
    }

    public BigDecimal getMonthlyExpense(int year, int month) throws SQLException {
        String sql = "SELECT COALESCE(SUM(amount), 0) FROM transactions " +
                    "WHERE type = 'EXPENSE' AND YEAR(transaction_date) = ? AND MONTH(transaction_date) = ?";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, year);
            pstmt.setInt(2, month);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return rs.getBigDecimal(1);
            }
            return BigDecimal.ZERO;
        }
    }

    public Map<String, BigDecimal> getIncomeByCategory(int year, int month) throws SQLException {
        String sql = "SELECT c.name, COALESCE(SUM(t.amount), 0) as total " +
                    "FROM categories c " +
                    "LEFT JOIN transactions t ON c.id = t.category_id " +
                    "AND t.type = 'INCOME' " +
                    "AND YEAR(t.transaction_date) = ? " +
                    "AND MONTH(t.transaction_date) = ? " +
                    "WHERE c.type = 'INCOME' " +
                    "GROUP BY c.id, c.name " +
                    "ORDER BY total DESC";
        
        Map<String, BigDecimal> result = new LinkedHashMap<>();
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, year);
            pstmt.setInt(2, month);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                result.put(rs.getString("name"), rs.getBigDecimal("total"));
            }
        }
        return result;
    }

    public Map<String, BigDecimal> getExpenseByCategory(int year, int month) throws SQLException {
        String sql = "SELECT c.name, COALESCE(SUM(t.amount), 0) as total " +
                    "FROM categories c " +
                    "LEFT JOIN transactions t ON c.id = t.category_id " +
                    "AND t.type = 'EXPENSE' " +
                    "AND YEAR(t.transaction_date) = ? " +
                    "AND MONTH(t.transaction_date) = ? " +
                    "WHERE c.type = 'EXPENSE' " +
                    "GROUP BY c.id, c.name " +
                    "ORDER BY total DESC";
        
        Map<String, BigDecimal> result = new LinkedHashMap<>();
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, year);
            pstmt.setInt(2, month);
            ResultSet rs = pstmt.executeQuery();
            
            while (rs.next()) {
                result.put(rs.getString("name"), rs.getBigDecimal("total"));
            }
        }
        return result;
    }

    public List<Map<String, Object>> getMonthlyTrend(int months) throws SQLException {
        String sql = "SELECT " +
                    "    DATE_FORMAT(transaction_date, '%Y-%m') as month, " +
                    "    COALESCE(SUM(CASE WHEN type = 'INCOME' THEN amount ELSE 0 END), 0) as income, " +
                    "    COALESCE(SUM(CASE WHEN type = 'EXPENSE' THEN amount ELSE 0 END), 0) as expense " +
                    "FROM transactions " +
                    "WHERE transaction_date >= DATE_SUB(CURDATE(), INTERVAL ? MONTH) " +
                    "GROUP BY DATE_FORMAT(transaction_date, '%Y-%m') " +
                    "ORDER BY month";
        
        List<Map<String, Object>> result = new ArrayList<>();
        LocalDate now = LocalDate.now();
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, months - 1);
            ResultSet rs = pstmt.executeQuery();
            
            // Create a map of all months in the range
            Map<String, Map<String, Object>> monthData = new TreeMap<>();
            for (int i = 0; i < months; i++) {
                YearMonth ym = YearMonth.from(now.minusMonths(i));
                String monthKey = ym.toString();
                Map<String, Object> data = new HashMap<>();
                data.put("month", monthKey);
                data.put("income", BigDecimal.ZERO);
                data.put("expense", BigDecimal.ZERO);
                monthData.put(monthKey, data);
            }
            
            // Fill in actual data
            while (rs.next()) {
                String month = rs.getString("month");
                Map<String, Object> data = monthData.get(month);
                if (data != null) {
                    data.put("income", rs.getBigDecimal("income"));
                    data.put("expense", rs.getBigDecimal("expense"));
                }
            }
            
            result.addAll(monthData.values());
        }
        return result;
    }

    public List<Transaction> getTransactions(Integer userId, LocalDate startDate, LocalDate endDate, 
            String type, Integer categoryId) throws SQLException {
        StringBuilder sql = new StringBuilder(
            "SELECT t.*, c.name as category_name, u.username as user_name " +
            "FROM transactions t " +
            "JOIN categories c ON t.category_id = c.id " +
            "JOIN users u ON t.created_by = u.id " +
            "WHERE t.transaction_date BETWEEN ? AND ? "
        );
        List<Object> params = new ArrayList<>();
        params.add(java.sql.Date.valueOf(startDate));
        params.add(java.sql.Date.valueOf(endDate));
        if (userId != null) {
            sql.append("AND t.created_by = ? ");
            params.add(userId);
        }
        if (type != null && !type.isEmpty()) {
            sql.append("AND t.type = ? ");
            params.add(type);
        }
        if (categoryId != null) {
            sql.append("AND t.category_id = ? ");
            params.add(categoryId);
        }
        sql.append("ORDER BY t.transaction_date DESC, t.created_at DESC");
        List<Transaction> transactions = new ArrayList<>();
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Transaction t = new Transaction();
                t.setId(rs.getInt("id"));
                t.setCreatedBy(rs.getInt("created_by"));
                t.setCategoryId(rs.getInt("category_id"));
                t.setAmount(rs.getBigDecimal("amount"));
                t.setType(Transaction.TransactionType.valueOf(rs.getString("type")));
                t.setDescription(rs.getString("description"));
                t.setTransactionDate(rs.getDate("transaction_date").toLocalDate());
                t.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
                t.setCategoryName(rs.getString("category_name"));
                t.setUserName(rs.getString("user_name"));
                transactions.add(t);
            }
        }
        return transactions;
    }

    public boolean addTransaction(Transaction transaction) throws SQLException {
        String sql = "INSERT INTO transactions (created_by, category_id, amount, type, description, transaction_date) " +
                    "VALUES (?, ?, ?, ?, ?, ?)";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, transaction.getCreatedBy());
            pstmt.setInt(2, transaction.getCategoryId());
            pstmt.setBigDecimal(3, transaction.getAmount());
            pstmt.setString(4, transaction.getType().name());
            pstmt.setString(5, transaction.getDescription());
            pstmt.setDate(6, java.sql.Date.valueOf(transaction.getTransactionDate()));
            return pstmt.executeUpdate() > 0;
        }
    }

    public boolean deleteTransaction(int transactionId, int userId) throws SQLException {
        String sql = "DELETE FROM transactions WHERE id = ? AND created_by = ?";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, transactionId);
            pstmt.setInt(2, userId);
            return pstmt.executeUpdate() > 0;
        }
    }

    public boolean updateTransaction(Transaction transaction) throws SQLException {
        String sql = "UPDATE transactions SET " +
                    "category_id = ?, amount = ?, type = ?, description = ?, transaction_date = ? " +
                    "WHERE id = ? AND created_by = ?";
        
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, transaction.getCategoryId());
            pstmt.setBigDecimal(2, transaction.getAmount());
            pstmt.setString(3, transaction.getType().name());
            pstmt.setString(4, transaction.getDescription());
            pstmt.setDate(5, java.sql.Date.valueOf(transaction.getTransactionDate()));
            pstmt.setInt(6, transaction.getId());
            pstmt.setInt(7, transaction.getCreatedBy());
            
            return pstmt.executeUpdate() > 0;
        }
    }

    public List<Map<String, Object>> getDailyIncomeExpense(int year, int month) throws SQLException {
        String sql = "SELECT DATE(transaction_date) as day, " +
                     "SUM(CASE WHEN type = 'INCOME' THEN amount ELSE 0 END) as income, " +
                     "SUM(CASE WHEN type = 'EXPENSE' THEN amount ELSE 0 END) as expense " +
                     "FROM transactions " +
                     "WHERE YEAR(transaction_date) = ? AND MONTH(transaction_date) = ? " +
                     "GROUP BY day ORDER BY day";
        List<Map<String, Object>> result = new ArrayList<>();
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, year);
            pstmt.setInt(2, month);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("day", rs.getString("day"));
                map.put("income", rs.getBigDecimal("income"));
                map.put("expense", rs.getBigDecimal("expense"));
                result.add(map);
            }
        }
        return result;
    }

    public Transaction getTransactionById(int id) throws SQLException {
        String sql = "SELECT * FROM transactions WHERE id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                Transaction t = new Transaction();
                t.setId(rs.getInt("id"));
                t.setCreatedBy(rs.getInt("created_by"));
                t.setCategoryId(rs.getInt("category_id"));
                t.setAmount(rs.getBigDecimal("amount"));
                t.setType(Transaction.TransactionType.valueOf(rs.getString("type")));
                t.setDescription(rs.getString("description"));
                t.setTransactionDate(rs.getDate("transaction_date").toLocalDate());
                t.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
                return t;
            }
        }
        return null;
    }

    // 管理员可编辑所有记录
    public boolean updateTransactionByAdmin(Transaction transaction) throws SQLException {
        String sql = "UPDATE transactions SET " +
                    "category_id = ?, amount = ?, type = ?, description = ?, transaction_date = ? " +
                    "WHERE id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, transaction.getCategoryId());
            pstmt.setBigDecimal(2, transaction.getAmount());
            pstmt.setString(3, transaction.getType().name());
            pstmt.setString(4, transaction.getDescription());
            pstmt.setDate(5, java.sql.Date.valueOf(transaction.getTransactionDate()));
            pstmt.setInt(6, transaction.getId());
            return pstmt.executeUpdate() > 0;
        }
    }

    // 管理员可删除所有记录
    public boolean deleteTransactionByAdmin(int transactionId) throws SQLException {
        String sql = "DELETE FROM transactions WHERE id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, transactionId);
            return pstmt.executeUpdate() > 0;
        }
    }

    /**
     * 获取每个用户的总收入和总支出
     */
    public List<Map<String, Object>> getTotalIncomeExpenseByUser() throws SQLException {
        String sql = "SELECT u.username, " +
                "COALESCE(SUM(CASE WHEN t.type = 'INCOME' THEN t.amount ELSE 0 END), 0) AS income, " +
                "COALESCE(SUM(CASE WHEN t.type = 'EXPENSE' THEN t.amount ELSE 0 END), 0) AS expense " +
                "FROM users u " +
                "LEFT JOIN transactions t ON u.id = t.created_by " +
                "GROUP BY u.id, u.username " +
                "ORDER BY u.username";
        List<Map<String, Object>> result = new ArrayList<>();
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("username", rs.getString("username"));
                map.put("income", rs.getBigDecimal("income"));
                map.put("expense", rs.getBigDecimal("expense"));
                result.add(map);
            }
        }
        return result;
    }
} 